/************************************************************************************/
/*  Program name CAPFNDEXT_MTL_PARA.sql                                             */
/*  Creater:  WONG Wing Kin (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 16 JAN, 2005   WONG Wing Kin     First created                                   */
/************************************************************************************/
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';
SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPFNDEXT_MTL_PARA';

	lv_Scc_Count	NUMBER			:=	0;
	lv_Err_Count	NUMBER			:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	lv_FileName		VARCHAR2(100)	:=	'CAPFND_MTL_PARA.dat';
	lv_PathName		VARCHAR2(100)	:=	'/tmp';
	lv_UTL_File		UTL_FILE.File_Type;

	lv_NGO			VARCHAR2(100);
	lv_NGO_Count	NUMBER;

	CURSOR Cur_F(p_NGO VARCHAR2) IS
	SELECT
		*
	FROM
		MTL_PARAMETERS
	WHERE	Organization_ID	IN	(
				SELECT
					O.Organization_ID
				FROM
					HR_ALL_ORGANIZATION_UNITS	A,
					HR_ALL_ORGANIZATION_UNITS	O
				WHERE	A.Name				=	p_NGO
				AND		O.Business_Group_ID	=	A.Organization_ID
				AND		O.Organization_ID	IN
							(
							SELECT	Organization_ID
							FROM	HR_ORGANIZATION_INFORMATION
							WHERE	Org_Information_Context	IN	('Accounting Information','Operating Unit Information')
							)
								);

BEGIN
	lv_NGO			:=	UPPER('&1');

	SELECT	COUNT('A')
	INTO	lv_NGO_Count
	FROM	HR_ALL_ORGANIZATION_UNITS
	WHERE	Name	=	lv_NGO;

	IF	lv_NGO_Count	>=	1	THEN
		lv_UTL_File		:=	UTL_FILE.FOpen(lv_PathName, lv_FileName, 'w');
		FOR C1 IN Cur_F(lv_NGO) LOOP
			UTL_FILE.PUT_LINE(
				lv_UTL_File,
					C1.Organization_ID||CHR(9)||				--MAPPING
				C1.Last_Update_Date||CHR(9)||
					C1.Last_Updated_By||CHR(9)||
				C1.Creation_Date||CHR(9)||
					C1.Created_By||CHR(9)||
				C1.Last_Update_Login||CHR(9)||
				C1.Organization_Code||CHR(9)||
					C1.Master_Organization_ID||CHR(9)||			--MAPPING
				C1.Primary_Cost_Method||CHR(9)||
					C1.Cost_Organization_ID||CHR(9)||			--MAPPING
				C1.Default_Material_Cost_ID||CHR(9)||
				C1.Calendar_Exception_Set_ID||CHR(9)||
				C1.Calendar_Code||CHR(9)||
				C1.General_Ledger_Update_Code||CHR(9)||
				C1.Default_Atp_Rule_ID||CHR(9)||
				C1.Default_Picking_Rule_ID||CHR(9)||
				C1.Default_Locator_Order_Value||CHR(9)||
				C1.Default_Subinv_Order_Value||CHR(9)||
				C1.Negative_Inv_Receipt_Code||CHR(9)||
				C1.Stock_Locator_Control_Code||CHR(9)||
					C1.Material_Account||CHR(9)||
					C1.Material_Overhead_Account||CHR(9)||
					C1.Matl_Ovhd_Absorption_Acct||CHR(9)||
					C1.Resource_Account||CHR(9)||
					C1.Purchase_Price_Var_Account||CHR(9)||
					C1.AP_Accrual_Account||CHR(9)||
					C1.Overhead_Account||CHR(9)||
					C1.Outside_Processing_Account||CHR(9)||
					C1.Intransit_Inv_Account||CHR(9)||
					C1.Interorg_Receivables_Account||CHR(9)||
					C1.Interorg_Price_Var_Account||CHR(9)||
					C1.Interorg_Payables_Account||CHR(9)||
					C1.Cost_Of_Sales_Account||CHR(9)||
					C1.Encumbrance_Account||CHR(9)||
					C1.Project_Cost_Account||CHR(9)||
					C1.Interorg_Transfer_CR_Account||CHR(9)||
				C1.MATL_INTERORG_TRANSFER_CODE||CHR(9)||
				C1.INTERORG_TRNSFR_CHARGE_PERCENT||CHR(9)||
					C1.SOURCE_ORGANIZATION_ID||CHR(9)||			--MAPPING
				C1.SOURCE_SUBINVENTORY||CHR(9)||
				C1.SOURCE_TYPE||CHR(9)||
				C1.Org_Max_Weight||CHR(9)||
				C1.Org_Max_Weight_Uom_Code||CHR(9)||
				C1.Org_Max_Volume||CHR(9)||
				C1.Org_Max_Volume_Uom_Code||CHR(9)||
				C1.Serial_Number_Type||CHR(9)||
				C1.Auto_Serial_Alpha_Prefix||CHR(9)||
				C1.Start_Auto_Serial_Number||CHR(9)||
				C1.Auto_Lot_Alpha_Prefix||CHR(9)||
				C1.Lot_Number_Uniqueness||CHR(9)||
				C1.Lot_Number_Generation||CHR(9)||
				C1.Lot_Number_Zero_Padding||CHR(9)||
				C1.Lot_Number_Length||CHR(9)||
				C1.Starting_Revision||CHR(9)||
				C1.Attribute_Category||CHR(9)||
				C1.Attribute1||CHR(9)||
				C1.Attribute2||CHR(9)||
				C1.Attribute3||CHR(9)||
				C1.Attribute4||CHR(9)||
				C1.Attribute5||CHR(9)||
				C1.Attribute6||CHR(9)||
				C1.Attribute7||CHR(9)||
				C1.Attribute8||CHR(9)||
				C1.Attribute9||CHR(9)||
				C1.Attribute10||CHR(9)||
				C1.Attribute11||CHR(9)||
				C1.Attribute12||CHR(9)||
				C1.Attribute13||CHR(9)||
				C1.Attribute14||CHR(9)||
				C1.Attribute15||CHR(9)||
				C1.Default_Demand_Class||CHR(9)||
				C1.Encumbrance_Reversal_Flag||CHR(9)||
				C1.Maintain_Fifo_Qty_Stack_Type||CHR(9)||
					C1.Invoice_Price_Var_Account||CHR(9)||
					C1.Average_Cost_Var_Account||CHR(9)||
					C1.Sales_Account||CHR(9)||
					C1.Expense_Account||CHR(9)||
				C1.Serial_Number_Generation||CHR(9)||
				C1.Request_ID||CHR(9)||
				C1.Program_Application_ID||CHR(9)||
				C1.Program_ID||CHR(9)||
				C1.Program_Update_Date||CHR(9)||
				C1.Global_Attribute_Category||CHR(9)||
				C1.Global_Attribute1||CHR(9)||
				C1.Global_Attribute2||CHR(9)||
				C1.Global_Attribute3||CHR(9)||
				C1.Global_Attribute4||CHR(9)||
				C1.Global_Attribute5||CHR(9)||
				C1.Global_Attribute6||CHR(9)||
				C1.Global_Attribute7||CHR(9)||
				C1.Global_Attribute8||CHR(9)||
				C1.Global_Attribute9||CHR(9)||
				C1.Global_Attribute10||CHR(9)||
				C1.Global_Attribute11||CHR(9)||
				C1.Global_Attribute12||CHR(9)||
				C1.Global_Attribute13||CHR(9)||
				C1.Global_Attribute14||CHR(9)||
				C1.Global_Attribute15||CHR(9)||
				C1.Global_Attribute16||CHR(9)||
				C1.Global_Attribute17||CHR(9)||
				C1.Global_Attribute18||CHR(9)||
				C1.Global_Attribute19||CHR(9)||
				C1.Global_Attribute20||CHR(9)||
				C1.Mat_Ovhd_Cost_Type_ID||CHR(9)||
				C1.Project_Reference_Enabled||CHR(9)||
				C1.Pm_Cost_Collection_Enabled||CHR(9)||
				C1.Project_Control_Level||CHR(9)||
				C1.Avg_Rates_Cost_Type_ID||CHR(9)||
				C1.Txn_Approval_Timeout_Period||CHR(9)||
				C1.Mo_Source_Required||CHR(9)||
				C1.Mo_Pick_Confirm_Required||CHR(9)||
				C1.Mo_Approval_Timeout_Action||CHR(9)||
					C1.Borrpay_Matl_Var_Account||CHR(9)||
					C1.Borrpay_Moh_Var_Account||CHR(9)||
					C1.Borrpay_Res_Var_Account||CHR(9)||
					C1.Borrpay_Osp_Var_Account||CHR(9)||
					C1.Borrpay_Ovh_Var_Account||CHR(9)||
				C1.Process_Enabled_Flag||CHR(9)||
				C1.Process_Orgn_Code||CHR(9)||
				C1.Wsm_Enabled_Flag||CHR(9)||
				C1.Default_Cost_Group_ID||CHR(9)||
				C1.Lpn_Prefix||CHR(9)||
				C1.Lpn_Suffix||CHR(9)||
				C1.Lpn_Starting_Number||CHR(9)||
				C1.Wms_Enabled_Flag||CHR(9)||
				C1.Pregen_Putaway_Tasks_Flag||CHR(9)||
				C1.Regeneration_Interval||CHR(9)||
				C1.Timezone_ID||CHR(9)||
				C1.Max_Picks_Batch||CHR(9)||
				C1.Default_Wms_Picking_Rule_ID||CHR(9)||
				C1.Default_Put_Away_Rule_ID||CHR(9)||
				C1.Default_Task_Assign_Rule_ID||CHR(9)||
				C1.Default_Label_Comp_Rule_ID||CHR(9)||
				C1.Default_Carton_Rule_ID||CHR(9)||
				C1.Default_Cyc_Count_Header_ID||CHR(9)||
				C1.Crossdock_Flag||CHR(9)||
				C1.Cartonization_Flag||CHR(9)||
				C1.Cost_Cutoff_Date||CHR(9)||
				C1.Enable_Costing_By_Category||CHR(9)||
					C1.Cost_Group_Accounting||CHR(9)||
				C1.Allocate_Serial_Flag||CHR(9)||
				C1.Default_Pick_Task_Type_ID||CHR(9)||
				C1.Default_CC_Task_Type_ID||CHR(9)||
				C1.Default_Putaway_Task_Type_ID||CHR(9)||
				C1.Default_Repl_Task_Type_ID||CHR(9)||
				C1.Eam_Enabled_Flag||CHR(9)||
					C1.Maint_Organization_ID||CHR(9)||			--MAPPING
				C1.Prioritize_Wip_Jobs||CHR(9)||
				C1.Default_Crossdock_Subinventory||CHR(9)||
				C1.Skip_Task_Waiting_Minutes||CHR(9)||
				C1.QA_Skipping_Insp_Flag||CHR(9)||
				C1.Default_Crossdock_Locator_ID||CHR(9)||
				C1.Default_Moxfer_Task_Type_ID||CHR(9)||
				C1.Default_Moissue_Task_Type_ID||CHR(9)||
				C1.Default_Matl_Ovhd_Cost_ID||CHR(9)||
				C1.Distributed_Organization_Flag||CHR(9)||
				C1.Carrier_Manifesting_Flag||CHR(9)||
				C1.Distribution_Account_ID||CHR(9)||
				C1.Direct_Shipping_Allowed||CHR(9)||
				C1.Default_Pick_Op_Plan_ID||CHR(9)||
				C1.Max_Clusters_Allowed||CHR(9)||
				C1.Consigned_Flag||CHR(9)||
				C1.Cartonize_Sales_Orders||CHR(9)||
				C1.Cartonize_Manufacturing||CHR(9)||
				CAPBREXT_COMMON.GetUser(C1.Last_Updated_By)||CHR(9)||
				CAPBREXT_COMMON.GetUser(C1.Created_By)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Material_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Material_Overhead_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Matl_Ovhd_Absorption_Acct)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Resource_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Purchase_Price_Var_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.AP_Accrual_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Overhead_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Outside_Processing_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Intransit_Inv_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Interorg_Receivables_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Interorg_Price_Var_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Interorg_Payables_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Cost_Of_Sales_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Encumbrance_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Project_Cost_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Interorg_Transfer_CR_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Invoice_Price_Var_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Average_Cost_Var_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Sales_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Expense_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Borrpay_Matl_Var_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Borrpay_Moh_Var_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Borrpay_Res_Var_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Borrpay_Osp_Var_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Borrpay_Ovh_Var_Account)||CHR(9)||
				CAPBREXT_COMMON.GetSegment(C1.Cost_Group_Accounting)
			);
			lv_Scc_Count	:=	lv_Scc_Count	+	1;
		END LOOP;
		IF	lv_Scc_Count	=	0	THEN
			lv_Err_Msg	:=	lv_NGO||': No MTL Parameters for this business group';
		END IF;
		UTL_FILE.FClose(lv_UTL_File);
	ELSE
		lv_Err_Count	:=	lv_Err_Count + 1;
		lv_Err_Msg		:=	lv_NGO||': No such BG exists';
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/